package cn.dida.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.springframework.stereotype.Repository;

import cn.dida.model.Pageable;
import cn.dida.util.Interval;

@Repository
public class QrcodeAutoProductDao  extends DaoBase {
	
public int maxexecutenub = 50000;
	

	public Pageable<Map<String, Object>> queryLgsQrcodeExtendList(Integer companyId, Map<String, Object> paramMap,	Integer page) {
		// TODO Auto-generated method stub  	
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from (select a.prod_billcode prodBillcode,b.`name` proName,a.prod_batchno prodBatchno, "
				+"a.is_act,a.prod_date,a.prod_fillingplantcode,Round(IFNULL(count(a.qrcode_id),0)/c.pcount,2) billNumber "
				+" from lgs_qrcode_extend a  "
				+"join product b on a.product_id = b.id  join pbc c on b.pbc_id = c.id ");
        
        StringBuffer sqlQueryResult = new StringBuffer("select a.prod_billcode prodBillcode,b.`name` proName,b.number proCode,a.prod_batchno prodBatchno, "
									+"a.is_act,a.prod_date,a.prod_fillingplantcode,a.pro_product_qrcode_id proproductqrcodeId,CONCAT(Round(IFNULL(count(a.qrcode_id),0)/c.pcount,2),'') billNumber "
									+" from lgs_qrcode_extend a  "
									+"join product b on a.product_id = b.id join pbc c on b.pbc_id = c.id ");

        StringBuffer sqlWhere = new StringBuffer(" where a.company_id = ? ");
       
        args = ArrayUtils.add(args, companyId);
        if(paramMap.containsKey("proName")){
        	sqlWhere.append(" and (b.`name` like ?  or b.number like ? ) ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("proName") + "%");
        	args = ArrayUtils.add(args, "%" + paramMap.get("proName") + "%");
        }
        
        if(paramMap.containsKey("fillingplantcode")){
        	sqlWhere.append(" and a.prod_fillingplantcode like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("fillingplantcode") + "%");
        }
        
        if(paramMap.containsKey("prodBillcode")){
        	sqlWhere.append(" and a.prod_billcode = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("prodBillcode"));
        }

        sqlWhere.append(" group by a.prod_billcode,b.`name`,a.is_act,a.prod_date,a.prod_fillingplantcode ");
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).append(" ) a ").toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY str_to_date(a.prod_date,'%Y%m%d %H:%i:%s') desc   limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
    }


	public Pageable<Map<String, Object>> queryLgsQrcodeExtendDetail(Integer companyId,String prodBillcode, Map<String, Object> paramMap,
			Integer page) {
		// TODO Auto-generated method stub
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
	
		Object[] args = {};
        StringBuffer sqlQueryResultCount = new StringBuffer("select count(1) from (select a.prod_billcode prodBillcode,b.`name` proName,a.prod_batchno prodBatchno,a.innerbox innerBox, "
									+"a.is_act,a.prod_date,a.prod_fillingplantcode,a.prod_groupcode,a.qrcode_id qrcodeId,a.outerbox_id outerboxId,a.c_time ctime "
									+" from lgs_qrcode_extend a  "
									+"join product b on a.product_id = b.id ");
        
        StringBuffer sqlQueryResult = new StringBuffer("select a.prod_billcode prodBillcode,b.`name` proName,a.prod_batchno prodBatchno,a.innerbox innerBox, "
									+"a.is_act,a.prod_date,a.prod_fillingplantcode,a.prod_groupcode,a.qrcode_id qrcodeId,a.outerbox_id outerboxId,a.c_time ctime "
									+" from lgs_qrcode_extend a  "
									+"join product b on a.product_id = b.id ");

        StringBuffer sqlWhere = new StringBuffer(" where a.company_id = ? and a.prod_billcode = ?  ");
       
        args = ArrayUtils.add(args, companyId);
        args = ArrayUtils.add(args, prodBillcode);
        if(paramMap.containsKey("prodBatchno")){
        	sqlWhere.append(" and a.prod_batchno = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("prodBatchno"));
        }
        
        if(paramMap.containsKey("groupcode")){
        	sqlWhere.append(" and a.prod_groupcode = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("groupcode"));
        }
        
        if(paramMap.containsKey("qrcodeId")){
        	sqlWhere.append(" and a.qrcode_id like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("qrcodeId") + "%");
        }
        
        if(paramMap.containsKey("outerboxId")){
        	sqlWhere.append(" and a.outerbox_id like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("outerboxId") + "%");
        }
        
        // 查询记录总数
        Integer resultTotal = jdbcTemplate.queryForObject(sqlQueryResultCount.append(sqlWhere).append(" ) a ").toString(), Integer.class, args);
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY a.outerbox_id  limit ?,?");
        args = ArrayUtils.add(args, (page - 1) * getPageSize());
        args = ArrayUtils.add(args, getPageSize());
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
        return new Pageable<>(page, getPageSize(), resultTotal, result);
	}


	public List<Map<String, Object>> queryLgsQrcodeExtendListNopage(Integer companyId, Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
		Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select a.prod_billcode prodBillcode,b.`name` proName,b.number proCode,a.prod_batchno prodBatchno, "
									+"case when a.is_act = 0 then '否' else '是' end is_act ,a.prod_date,a.prod_fillingplantcode,Round(IFNULL(count(a.qrcode_id),0)/c.pcount,2) billNumber "
									+" from lgs_qrcode_extend a  "
									+"join product b on a.product_id = b.id join pbc c on b.pbc_id = c.id ");

        StringBuffer sqlWhere = new StringBuffer(" where a.company_id = ? ");
        args = ArrayUtils.add(args, companyId);
        if(paramMap.containsKey("proName")){
        	sqlWhere.append(" and b.`name` like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("proName") + "%");
        }
        if(paramMap.containsKey("fillingplantcode")){
        	sqlWhere.append(" and a.prod_fillingplantcode like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("fillingplantcode") + "%");
        }
        if(paramMap.containsKey("prodBillcode")){
        	sqlWhere.append(" and a.prod_billcode = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("prodBillcode"));
        }
        sqlWhere.append(" group by a.prod_billcode,b.`name` ,a.prod_batchno,a.is_act,a.prod_date,a.prod_fillingplantcode ");
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY str_to_date(a.prod_date,'%Y%m%d %H:%i:%s') desc  ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
		return result;
	}


	public List<Map<String, Object>> queryLgsQrcodeExtendDetailNopage(Integer companyId,Map<String, Object> paramMap) {
		// TODO Auto-generated method stub
		if(null==paramMap){
			paramMap = new HashMap<String, Object>();
		}
		Object[] args = {};
        StringBuffer sqlQueryResult = new StringBuffer("select a.prod_billcode prodBillcode,b.`name` proName,a.prod_batchno prodBatchno,a.innerbox innerBox, "
									+"a.is_act,a.prod_date,a.prod_fillingplantcode,a.prod_groupcode,a.qrcode_id qrcodeId,a.outerbox_id outerboxId,substr(FROM_UNIXTIME(a.c_time/1000),1,19) ctime "
									+" from lgs_qrcode_extend a  "
									+"join product b on a.product_id = b.id ");

        StringBuffer sqlWhere = new StringBuffer(" where a.company_id = ? ");
       
        args = ArrayUtils.add(args, companyId);
        if(paramMap.containsKey("prodBatchno")){
        	sqlWhere.append(" and a.prod_batchno = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("prodBatchno"));
        }
        
        if(paramMap.containsKey("groupcode")){
        	sqlWhere.append(" and a.prod_groupcode = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("groupcode"));
        }
        if(paramMap.containsKey("prodBillcode")){
        	sqlWhere.append(" and a.prod_billcode = ?  ");
        	args = ArrayUtils.add(args, paramMap.get("prodBillcode"));
        }
        if(paramMap.containsKey("qrcodeId")){
        	sqlWhere.append(" and a.qrcode_id like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("qrcodeId") + "%");
        }
        if(paramMap.containsKey("outerboxId")){
        	sqlWhere.append(" and a.outerbox_id like ?  ");
        	args = ArrayUtils.add(args, "%" + paramMap.get("outerboxId") + "%");
        }
        // 查询记录
        sqlQueryResult.append(sqlWhere).append("  ORDER BY a.outerbox_id  ");
        List<Map<String,Object>> result = jdbcTemplate.queryForList(sqlQueryResult.toString(), args);
		return result;
	}
	
	/**
	 * 获取自动绑定瓶码的最大值和最小值
	 * @author kelan
	 * @date 2018年1月23日
	 * @param prod_billcode
	 * @param companyid
	 * @return
	 */
	public List<Map<String, Object>> selectMinIDAndMaxIDBylgsQrcodeExtendQrcodeId(String prod_billcode, Integer companyid) {
		// TODO Auto-generated method stub
		
		String sql = "select max(qrcode_id)maxId,min(qrcode_id)minId,a.qrcode_baseid,a.product_id,a.prod_batchno from lgs_qrcode_extend a  "
		+"where a.company_id = ? and a.prod_billcode=?  "
		+"group by a.qrcode_baseid  ";
		
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, companyid,prod_billcode);
		
		return list;
	}
	
	/**
	 * 获取自动绑定箱码的最大值和最小值
	 * @author kelan
	 * @date 2018年1月23日
	 * @param prod_billcode
	 * @param companyid
	 * @return
	 */
	public List<Map<String, Object>> selectMinIDAndMaxIDBylgsQrcodeExtendOuterboxeId(String prod_billcode, Integer companyid) {
		// TODO Auto-generated method stub
		
		String sql = "select max(outerbox_id)maxId,min(outerbox_id)minId,a.outerbox_baseid qrcode_baseid,a.product_id,a.prod_batchno from lgs_qrcode_extend a  "
		+"where a.company_id = ? and a.prod_billcode=?  "
		+"group by a.outerbox_baseid  ";
		
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, companyid,prod_billcode);
		
		return list;
	}
	
	
	/**
	 * 获取二维码 和 产品的绑定记录
	 * @author kelan
	 * @date 2018年1月23日
	 * @param qcid
	 * @param product_id
	 * @return
	 */
	
	public List<Map<String, Object>>  selectBandingOfQrcodeList(Long qcid, Integer product_id) {
		
		String sql = "select p.id,p.qrcode_s,p.qrcode_e,qrcode_count from pro_product_qrcode p where p.qrcode_base_id = ? and p.product_id = ? and p.bindtype = 1 ";
		
		List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, qcid,product_id);
		
		return list;
	}
	
	
	/**
	 * 更新PIN码 qrcode_detail_1表的 pro_product_qrcode_id 字段
	 * @author kelan
	 * @date 2018年1月23日
	 * @param qcid
	 * @param ppqid
	 * @param product_id
	 * @param prod_billcode
	 * @param companyid
	 * @return
	 */
	public Integer updateQrcodeDetailOfProduct(Long qcid,Integer ppqid, Integer product_id, String prod_billcode,Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+companyid+" qr set qr.pro_product_qrcode_id = ? where qr.qrcode_base_id = ? and qr.id = ( "
				+"select a.qrcode_id from lgs_qrcode_extend a where qr.id = a.qrcode_id and a.company_id = '"+companyid+"' and a.product_id='"+product_id+"' "
				+ "and a.prod_billcode = '"+prod_billcode+"' "
				+" group by a.qrcode_id )" ;
		return jdbcTemplate.update(sql,ppqid,qcid);
	}
	
	/**
	 * 更新箱码 qrcode_detail_1表的 pro_product_qrcode_id 字段
	 * @author kelan
	 * @date 2018年1月23日
	 * @param qcid
	 * @param ppqid
	 * @param product_id
	 * @param prod_billcode
	 * @param companyid
	 * @return
	 */
	public Integer updateQrcodeBoxDetailOfProduct(Long qcid,Integer ppqid, Integer product_id, String prod_billcode,Integer companyid) {
		// TODO Auto-generated method stub
		String sql = "update qrcode_detail_"+companyid+" qr set qr.pro_product_qrcode_id = ? where qr.qrcode_base_id = ? and qr.id = ( "
				+"select a.outerbox_id from lgs_qrcode_extend a where qr.id = a.outerbox_id and a.company_id = '"+companyid+"' and a.product_id='"+product_id+"' "
				+ "and a.prod_billcode = '"+prod_billcode+"' "
				+" group by a.outerbox_id )" ;
		return jdbcTemplate.update(sql,ppqid,qcid);
	}
	
	/**
	 * 更新自动绑定 表的是否启用营销字段和pro_product_qrcode_id字段
	 * @author kelan
	 * @date 2018年1月23日
	 * @param qcid
	 * @param ppqid
	 * @param product_id
	 * @param prod_billcode
	 * @param companyid
	 * @return
	 */
	public Integer updateLgsQrcodeExtendByisact(Long qcid,Integer ppqid, Integer product_id, String prod_billcode,Integer companyid,Integer act) {
		// TODO Auto-generated method stub
		String sql = "update lgs_qrcode_extend a set a.is_act = ?,a.pro_product_qrcode_id =?  WHERE a.prod_billcode = ? and a.product_id= ? and a.company_id =? " ;
		return jdbcTemplate.update(sql,act,ppqid,prod_billcode,product_id,companyid);
	}
	
	
	/**
	 * 更新pro_product_qrcode表 码段值
	 * @author kelan
	 * @date 2018年1月24日
	 * @param ppqid
	 * @param start
	 * @param end
	 * @return
	 */
	public Integer updateProProductQrcodeByqrcode_s_e(Integer ppqid, Long start,Long end) {
		// TODO Auto-generated method stub
		String sql = "update pro_product_qrcode p set p.qrcode_s= ? ,p.qrcode_e=?,p.qrcode_count=? where p.id =? " ;
		return jdbcTemplate.update(sql,start,end,end-start+1,ppqid);
	}
	
	
	
}
